﻿CREATE PROCEDURE [dbo].[Report_InventoryOfAnalyzedArticles]
 @RangeReviewId int
  , @ExchangeRate smallint = 0
AS
	
declare @startDate DATE = (select FirstMonth from RangeReviews WITH (NOLOCK) where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews WITH (NOLOCK) where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @maxDate DATE = (Select MAX(Date) From Inventories i WITH (NOLOCK) Inner Join Articles a WITH (NOLOCK) on i.ArticleId = a.Id and a.RangeReviewId = @RangeReviewId)

begin try drop table #exchangeRates; end try begin catch end catch;

Select 
	@maxDate Date
	, CASE 
		WHEN @ExchangeRate = 0 THEN 1 --Moneda Local
		WHEN @ExchangeRate = 1 THEN ISNULL(ex.Rate,1) --Rate para Average
		WHEN @ExchangeRate = 2 THEN ISNULL(ex.ForexRate,1) --Rate para Same Store
		ELSE 1
	END AS Rate
Into #exchangeRates
From dbo.RangeReviews rr WITH (NOLOCK)
INNER JOIN dbo.Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId
LEFT OUTER JOIN dbo.CurrencyExchangeRates ex WITH (NOLOCK) on ex.Date = @maxDate and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
Where rr.Id = @RangeReviewId

SELECT
	ar.Id
	, ar.Code
	, ar.UOM
	, ar.Name
	, ar.Delist
	, ar.IsDelisted
	, ar.IsNew
	, ar.IsDeletedRangeReview
	, parentCat.Name as Category
	, ISNULL(SUM(inv.Cost * ex.Rate) ,0) as InventoryCost
FROM 
	Articles ar --Articulos analizados en el RR.
	INNER JOIN RangeReviews rr WITH (NOLOCK) on ar.RangeReviewId = rr.Id and rr.Id = @RangeReviewId
	INNER JOIN Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id 
	INNER JOIN Categories parentCat WITH (NOLOCK) on cat.ParentId = parentCat.Id
	INNER JOIN Countries co WITH (NOLOCK) on rr.CountryId = co.Id
	INNER JOIN Currencies cur WITH (NOLOCK) on rr.CountryId = cur.CountryId	
	LEFT JOIN Inventories inv WITH (NOLOCK) on inv.ArticleId = ar.Id and inv.Date = @maxDate
	LEFT JOIN #exchangeRates ex WITH (NOLOCK) on ex.Date = inv.Date
Where
	ar.RangeReviewId = @RangeReviewId 
	and ar.IsDeletedPerformance = 0
	and parentCat.ParentId = 6
Group by
	ar.Id
	, ar.Code
	, ar.UOM
	, ar.Name
	, ar.Delist
	, ar.IsDelisted
	, ar.IsNew
	, ar.IsDeletedRangeReview
	, parentCat.Name
	, co.IsVATIncluded

drop table #exchangeRates